In a multi-user environment, Relational Data Base Management System (RDBMS) efficiently manages and executes concurrent user queries. The user queries are performed in separate threads of execution and are framed to access data entities associated with the RDBMS, hereinafter referred as database system. In the database system, a single data-entity, for example a database, a table, a row, a cell, and the like, is shared and accessed by multiple threads, which brings the issues of concurrency and data consistency. In an example, the row is a record of the database table.
Presently, for concurrency and data consistency, a mechanism of locking is used in the database system that enforces mutually exclusive access to shared data entities. As a result, only one thread can access a shared data entity at a time. However, this mechanism degrades the database system performance depending upon lock's granularity. For example, a row-lock is more granular than a table-lock. Thus, when a column (or a field) in a row (or a record) is being accessed, then all the other columns corresponding to that row are locked and no update operations can be performed on the other columns.
The database system maintains a global list of locks where each lock entry contains information about a thread that has acquired the lock and information about the shared object being locked. Therefore, if a thread wants to access a shared data object, it will first request a lock from the database system. The database system will then scan through the global lock-list, and check whether the same shared object has been locked by any other thread or not. If the object is not locked, then the database system adds a new entry into lock-list referring to the new thread's exclusive access to the data object. Thus, the thread is allowed to access and modify the contents of the shared object.
After accessing the data object, the thread can request the database system to release the lock that, in turn, deletes the earlier-added entry. The shared data object is then unlocked and is free for access by other threads. Thus, the task of maintaining lock consumes considerable amount of memory and CPU resources. Further, it becomes memory and CPU intensive when large number of data objects is being locked.
Currently, the row level lock is the most granular lock offered by the current implementations of locking mechanism. Therefore, to increase the performance, the row-level locking enables a table scan that allows only read operations on the locked columns of the row, provided that the read operation does not involve the columns being modified by a writer thread. Thus, in a database table, when a transaction intends to perform an update query on a column of the row, the locking mechanism locks the complete row while the update operation is being performed on the column. Hence, other transactions can only perform read operations on the unlocked columns of the row, and cannot update the column. This degrades the performance of the database system.